#!/bin/bash

cpu_data_get(){
    used_percent=$(top -bn1|awk -F'[ ,]+' 'NR==3{print $8}')
    free_percent=$(echo "100-$used_percent"|bc)
    used_percent=${used_percent}%
    free_percent=${free_percent}%
    data="'$host','$time','$used_percent','$free_percent'"
    table="dbserver.cpu_cap"
    sql_insert "$table" "$data"
}

mem_data_get(){
    used_size=$(free -m|awk 'NR==2{print $3}')
    free_size=$(free -m|awk 'NR==2{print $4}')
    total_size=$(free -m|awk 'NR==2{print $2}')
    used_percent=$(free -m|awk 'NR==2{printf "%.2f%%\n",$3/$2*100}')
    data="'$host','$time','$used_size','$free_size','$used_percent','$total_size'"
    table="dbserver.mem_cap"
    sql_insert "$table" "$data"
}

diskinfo(){
    df -Tm|egrep "xfs|ext4"| while read part_name fs total_size used_size free_size used_percent mountpoint
    do
        data="'$host','$time','$part_name','$fs',$used_size,$free_size,'$used_percent',$total_size,'$mountpoint'"
	table="dbserver.diskinfo"
	sql_insert "$table" "$data"
    done
}

mysql_info(){
    pid=$(ps aux|grep mysqld|awk 'NR==1{print $2}')
    process=$(ps aux|grep mysqld|awk 'NR==1{print $11}')
    port1=$(netstat -anplut|grep mysqld|awk 'NR==1{print $4}'|sed -n 's/.*://p')
    port2=$(netstat -anplut|grep mysqld|awk 'NR==2{print $4}'|sed -n 's/.*://p')
    runtime=$(ps aux|grep mysqld|awk 'NR==1{print $10}')
    table='dbserver.mysql_statu'
    if ssh 192.168.88.139 "pidof mysqld" &>/dev/null ;then 
        statu='0'
	while IFS=, read host1 time1 statu1 ;
	do
	    data1="'$host1','$time1','$statu1'"
	    sql="insert into ${table}(host,time,statu) values($data1)"
            mysql -uam123 -p@Zf123456 -h 192.168.88.139 -e "$sql" 2&>/dev/null
	done < ./mysql_stop_statu
	>./mysql_stop_statu
	data="'$host','$time',$pid,$port1,$port2,'$runtime','$process','$statu'"
	sql_insert "$table" "$data" 
    else
	statu='1'
	data="$host,$time,$statu"
	echo $data >>./mysql_stop_statu
	exit
    fi
}

sql_insert(){
    table=$1
    data=$2
    sql="insert into $table values($data);"
    #echo $sql
    #mysql -uam123 -p@Zf123456 -h 192.168.88.139 -e "$sql"
    mysql -uam123 -p@Zf123456 -h 192.168.88.139 -e "$sql" 2>/dev/null
}

host=$(hostname)
time=$(date "+%Y-%m-%d %H:%M:%S")

mysql_info
cpu_data_get
mem_data_get
diskinfo
